﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using System.Collections;

namespace ImpOracleFromExcel
{
    public class OracleHelper
    {
        private string _connString;
        private FrmMain _app;

        public OracleHelper(string strDB, string strUser, string strPwd, FrmMain app)
        {
            _app = app;
            _connString = "Data Source=" + strDB + ";User ID=" + strUser + ";Password=" + strPwd + ";";
        }

        public List<string> GetTables()
        {
            List<string> tables = new List<string>();

            using (OracleConnection conn = new OracleConnection(_connString))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand("select table_name from user_tables", conn);
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt != null)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        tables.Add(dr["table_name"].ToString());
                    }
                }
            }

            return tables;
        }

        public void GetColumns(string table_name, Hashtable ht)
        {
            ht.Clear();
            using (OracleConnection conn = new OracleConnection(_connString))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand(
                    "select column_name,data_type from user_tab_columns where table_name='" + table_name + "'", conn);
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt != null)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        string column_name = dr["column_name"].ToString();
                        OracleType ot = OracleType.VarChar;
                        try
                        {
                            string strType = dr["data_type"].ToString();
                            if (strType == "VARCHAR2")
                            {
                                ot = OracleType.VarChar;
                            }
                            if (strType == "NVARCHAR2")
                            {
                                ot = OracleType.NVarChar;
                            }
                            if (strType == "NUMBER")
                            {
                                ot = OracleType.Number;
                            }
                            if (strType == "CLOB")
                            {
                                ot = OracleType.Clob;
                            }
                            if (strType == "BLOB")
                            {
                                ot = OracleType.Blob;
                            }
                            if (strType == "DATE")
                            {
                                ot = OracleType.DateTime;
                            }
                            //ot = (OracleType)Enum.Parse(typeof(OracleType), dr["data_type"].ToString(), false);
                        }
                        catch
                        {
                            ot = OracleType.VarChar;
                        }

                        if (!ht.Contains(column_name))
                        {
                            ht.Add(column_name, ot);
                        }

                    }
                }
            }
        }

        public void ExecuteNonQuery(string sql, OracleParameter[] ops)
        {
            using (OracleConnection conn = new OracleConnection(_connString))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand(sql, conn);
                cmd.CommandType = CommandType.Text;
                foreach (OracleParameter op in ops)
                {
                    cmd.Parameters.Add(op);
                }
                cmd.ExecuteNonQuery();
            }
        }
    }
}
